﻿CREATE PROCEDURE [dbo].[Articles_Join]
	@ArticleIds varchar(max)
	, @TargetArticleId int
AS

--Seteo los registros de ventas como Confirmed en false
Update ArticleSales
Set Confirmed = 0
Where ArticleId in (Select Id from dbo.Split(@ArticleIds, ','))

--Inserto las Ventas sumarizadas
declare @targetUOM varchar(50) = (select UOM from Articles where Id = @TargetArticleId);

Insert Into ArticleSales (StoreId, ArticleId, Date, Units, Sales, GM, GrossSales, Price, Confirmed, BonusBuyNumber)
Select 
	StoreId, 
	@TargetArticleId, 
	Date, 
	SUM(Units / isnull(co.Value, 1)), 
	SUM(Sales), 
	SUM(GM), 
	SUM(GrossSales), 
	CASE WHEN SUM(Units) > 0 THEN SUM(GrossSales) / SUM(Units) ELSE 0 END AS Price, 
	1,
	BonusBuyNumber
From ArticleSales s
Inner join Articles a on s.ArticleId = a.Id
--Inner join Articles ta on 1 = ta.Id
left join UnitOfMeasureConvertions co on co.SourceUnit = a.UOM and TargetUnit = @targetUOM
Where ArticleId in (Select Id from dbo.Split(@ArticleIds, ','))
and (Deleted is null or Deleted = 0)
group by StoreId, Date, BonusBuyNumber

--Elimino las ventas anteriores
Update ArticleSales
Set Deleted = 1
Where ArticleId in (Select Id from dbo.Split(@ArticleIds, ','))
  and Confirmed = 0


--Borrado Logico del Articulo
Update Articles
Set IsDeletedRangeReview = 1, IsDeletedPerformance = 1
Where Id in (Select Id from dbo.Split(@ArticleIds, ',')) and Id <> @TargetArticleId

--Genero el Audit Trail
INSERT INTO AuditTrails ([Source],[Date],[Comments])
Select 'ArticleMasterJoin', GETDATE(), 'El articulo ' + Code + ' ' + Name + ' fue reemplazado por el articulo ' + (Select Code + ' ' + Name From Articles Where Id = @TargetArticleId)
From Articles Where Id in (Select Id from dbo.Split(@ArticleIds, ',')) and Id <> @TargetArticleId